"""
飞检数据STEP8筛选 - clue_bad_chemic.py
============================================================================

将 关联物价表与各个医院实际的化验收费数据，将有可能存在串码线索的项目找出来 。

主要功能：
1. 连接数据库
2. 读入 hspList.json 文件，获取医院列表
3. 执行 SQL 查询，找出可能存在串码线索的化验项目
4. 将结果保存为 excel 文件
    4.1. 保存到 STEP8筛选/clue 目录下，clue862_化验方法串码线索_内部_{hsp_abbr}.xlsx
    4.2. 用 openpyxl 读取，再次调整格式

============================================================================
"""


import os
import re
import sys
import time
import pandas as pd
from sqlalchemy import text
import json
import openpyxl

sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
from config import create_db_engine

_ILLEGAL_CHAR_RE = re.compile(r"[\x00-\x08\x0b\x0c\x0e-\x1f]")

def clean_illegal_chars(s: str) -> str:
    """Remove illegal characters from a string."""
    if pd.isna(s):
        return s
    return _ILLEGAL_CHAR_RE.sub("", s)


# 时间函数
t0 = time.time()
def elapsed() -> str:
    timeStr = time.strftime("%H:%M:%S", time.localtime())
    delta = int(time.time() - t0)
    if delta < 60:
        return f"{timeStr} (+ {delta} sec)"
    elif delta < 3600:
        m, s = divmod(delta, 60)
        return f"{timeStr} (+ {m} min {s} sec)"
    elif delta < 86400:
        h, rem = divmod(delta, 3600)
        m, s = divmod(rem, 60)
        return f"{timeStr} (+ {h} hour {m} min {s} sec)"
    else:
        d, rem = divmod(delta, 86400)
        h, rem = divmod(rem, 3600)
        m, s = divmod(rem, 60)
        return f"{timeStr} (+ {d} day {h} hour {m} min {s} sec)"
    
# 创建数据库引擎
engine = create_db_engine()

# 读入医院列表
def get_hsp_list():
    with open('hspList.json', 'r', encoding='utf-8') as f:
        hsp_list = json.load(f)
    return hsp_list

# 查询SQL
def query_clue_bad_chemic(hsp_abbr: str):
    sql = text(f"""
        with t0 as (
            select ssp.hsp_abbr, rp.value_raw as item_code, ssp.item_code as item_med_code, ssp.item_name, ssp.price
            from stable_serv_price ssp right outer join rule_package rp on ssp.item_code = rp.package
            where ssp.hsp_abbr = :hsp_abbr
        )
        -- select * from t0 where t0.item_code like '%250310014%';
        ,t1a as (
        -- 将涉及到方法的化验项目筛选出来
        select t0.*, substr(t0.item_code,3,9) as item_ptn,
            -- 化验模式名称
            REGEXP_SUBSTR(t0.item_name, '^(.*)-(.*)$', 1, 1, NULL, 1) as item_ptn_name,
            REGEXP_SUBSTR(t0.item_name, '^(.*)-(.*)$', 1, 1, NULL, 2) as item_method,
            -- 物价排名
            dense_rank() over(partition by substr(t0.item_code,3,9) order by t0.price) as price_rank
        from t0
        where substr(t0.item_code,3,2)='25'
            and item_name like '%法'
        order by t0.item_code
        )
        ,t1b as (
        select t1a2.item_ptn, t1a2.item_code, t1a2.item_method, listagg(distinct t1a1.item_method,'、') within group(order by t1a1.price_rank) as lower_methods
        from t1a t1a1, t1a t1a2
        where t1a1.item_ptn = t1a2.item_ptn and t1a1.price_rank<t1a2.price_rank
        group by t1a2.item_method, t1a2.item_ptn, t1a2.item_code
        )
        -- select * from t1b where t1b.item_code like '%250310014%';  --debug
        ,t1 as (
        select t1a.*, 
            case when t1b.lower_methods is null then null else '可能实际执行的方法学是：'||t1b.lower_methods end as msg
        from t1a left outer join t1b on t1a.item_code = t1b.item_code
        )
        -- select * from t1 where t1.item_ptn = '250310014';
        ,t3 as (
        -- 将当前医院下在 price_rank>1 的数据中有出现过的 ptn 筛选出来
        select distinct t1.item_ptn
        from t1
        where exists(
            SELECT 1 FROM scene_item_ext sie 
            WHERE t1.hsp_abbr = sie.hsp_abbr AND t1.price_rank>1 AND (
            t1.item_code = sie.item_code OR t1.item_med_code = sie.item_code OR t1.item_name = sie.item_name OR t1.item_name = sie.item_hsp_name
            )
        ) 
        )
        ,t4 as (
        -- 将 t2, t3 剩余的ptn放出来，同时按价格升序排序
        select t1.*
        from t1, t3
        where t1.item_ptn = t3.item_ptn
        order by t1.item_ptn, t1.price
        )
        select
        t4.item_ptn, t4.item_code as 三目编码, t4.price as 三目价格, t4.item_name as 三目名称,
        t4.item_ptn_name as 化验项目, t4.item_method as 方法学, 
        max(sie.p) as 医院最大单价,
        min(sie.p) as 医院最小单价,
        sum(sie.sum_q) as 总数量,
        sum(sie.sum_c) as 总金额,
        sum(sie.sum_b) as 总医保内金额,
        sum(sie.used) as 使用病例数,
        t4.msg as 串换线索
        from t4 left outer join scene_item_ext sie on (
        (t4.item_code = sie.item_code OR t4.item_med_code = sie.item_code OR t4.item_name = sie.item_name OR t4.item_name = sie.item_hsp_name)
        and t4.hsp_abbr = sie.hsp_abbr
        )
        group by 
        t4.item_ptn, t4.price, t4.item_code, t4.item_name, t4.price_rank, t4.item_ptn_name, t4.item_method, t4.msg
        having sum(sie.sum_q)>0 or t4.price_rank=1
        order by t4.item_ptn, t4.price
    """)
    with engine.connect() as conn:
        result = conn.execute(sql, {"hsp_abbr": hsp_abbr})
        df = pd.DataFrame(result.fetchall(), columns=result.keys())
    return df

def save_to_excel(df: pd.DataFrame, hsp_abbr: str) -> None:
    output_pre_dir = os.path.join('STEP8筛选', 'clue')
    os.makedirs(output_pre_dir, exist_ok=True)
    output_dir = os.path.join('STEP8筛选', 'clue', hsp_abbr)
    os.makedirs(output_dir, exist_ok=True)
    output_path = os.path.join(output_dir, f'clue862_化验方法串码线索_内部_{hsp_abbr}.xlsx')
    df.to_excel(output_path, index=False)

    # 使用 openpyxl 重新调整格式
    wb = openpyxl.load_workbook(output_path)
    ws = wb.active

    # 不同的 item_ptn 使用不同的颜色填充
    fill_colors = ['FFFFFF', 'FFEEEE', 'EEFFEE', 'EEEEFF', 'FFFFEE', 'EEFFFF', 'FFEEFF']
    current_ptn = None
    current_fill = None
    color_index = 0
    for row in ws.iter_rows(min_row=2):
        item_ptn = row[0].value
        if item_ptn != current_ptn:
            current_ptn = item_ptn
            current_fill = openpyxl.styles.PatternFill(start_color=fill_colors[color_index % len(fill_colors)],
                                                       end_color=fill_colors[color_index % len(fill_colors)],
                                                       fill_type='solid')
            color_index += 1
        for cell in row:
            cell.fill = current_fill
            if isinstance(cell.value, str):
                cell.value = clean_illegal_chars(cell.value)

    # 保存修改
    wb.save(output_path)

# 主函数
if __name__ == "__main__":
    print(f"[{elapsed()}] Starting step8_6_5_clue_bad_chemic.py...")

    hsp_list = get_hsp_list()
    total_hsps = len(hsp_list)
    for idx, hsp in enumerate(hsp_list):
        hsp_abbr = hsp['hsp_abbr']
        print(f"[{elapsed()}] Processing hospital {idx + 1}/{total_hsps}: {hsp_abbr}...")
        df_clue = query_clue_bad_chemic(hsp_abbr)
        if not df_clue.empty:
            save_to_excel(df_clue, hsp_abbr)
            print(f"[{elapsed()}] Saved clue data for {hsp_abbr}, rows: {len(df_clue)}")
        else:
            print(f"[{elapsed()}] No clue data found for {hsp_abbr}.")
    
    print(f"[{elapsed()}] step8_6_5_clue_bad_chemic.py completed.")